﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraTabbedMdi;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using Microsoft.VisualBasic;
using System.Linq;
using DevEx.Thanh.Class;
using Excel = Microsoft.Office.Interop.Excel;

namespace DevEx
{
    class CModule_Sy
    {
        public void ChuyenSoLieu()
        {
            QLVTDataContext dc = new QLVTDataContext();
            //Chuyen so lieu tu excel sang store and don vi
            //Microsoft.Office.Interop.Excel._Application oXL; 
            object oXL;
            Workbook oWb;
            Worksheet oSheetHSLL;
            Worksheet osheetQTCB = new Worksheet();
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            Type t = Type.GetTypeFromProgID("Excel.Application");
            oXL = System.Activator.CreateInstance(t);

            //mở file excel
            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                oWb = ((Microsoft.Office.Interop.Excel.Application)oXL).Workbooks.Open(openFileDialog1.FileName);

                osheetQTCB = (Microsoft.Office.Interop.Excel.Worksheet)oWb.Worksheets.get_Item("XEMAY");
                //oXL.Visible = True
                //thêm Column vào bảng

                for (int i = 153; i <= 163; i++) //
                {
                    tblTon objTon = new tblTon();
                    
                    //1. DONG CO MAY BAY 8 - 1117
                    //String MaLoaiVatTu = "A0100100000000";

                    //if (i <= 216)
                    //    objTon.ID_Kho = 1;
                    //else
                    //{
                    //    if (i == 217)
                    //    {
                            
                    //        continue;
                    //    }
                    //    else
                    //        if (i <= 424)
                    //            objTon.ID_Kho = 2;
                    //        else
                    //            if (i == 425)
                    //            {
                                    
                    //                continue;
                    //            }
                    //            else
                    //                if (i <= 1039)
                    //                    objTon.ID_Kho = 4;
                    //                else
                    //                    if (i == 1040)
                    //                    {
                                            
                    //                        continue;
                    //                    }
                    //                    else
                    //                        objTon.ID_Kho = 3;
                    //}

                    //2. DIEN 8 - 61
                    //String MaLoaiVatTu = "A0100200100000";
                    //if (i <= 21)
                    //    objTon.ID_Kho = 1;
                    //else
                    //    if (i == 22)
                    //        continue;
                    //    else
                    //        if (i <= 38)
                    //            objTon.ID_Kho = 2;
                    //        else
                    //            if (i == 39)
                    //                continue;
                    //            else
                    //                objTon.ID_Kho = 3;

                    //3. DH 8 - 132
                    //String MaLoaiVatTu = "A0100200200000";
                    //if (i <= 57)
                    //    objTon.ID_Kho = 1;
                    //else
                    //    if (i == 58)
                    //        continue;
                    //    else
                    //        if (i <= 98)
                    //            objTon.ID_Kho = 2;
                    //        else
                    //            if (i == 99)
                    //                continue;
                    //            else
                    //                objTon.ID_Kho = 3;

                    //4. VOTUYEN 8 - 64
                    //String MaLoaiVatTu = "A0100300000000";
                    //if (i <= 18)
                    //    objTon.ID_Kho = 1;
                    //else
                    //    if (i == 19)
                    //        continue;
                    //    else
                    //        if (i <= 55)
                    //            objTon.ID_Kho = 2;
                    //        else
                    //            if (i == 56)
                    //                continue;
                    //            else
                    //                objTon.ID_Kho = 3;


                    //5. LINHKIEN 8 - 437
                    //String MaLoaiVatTu = "A0100500000000";
                    //if (i <= 49)
                    //    objTon.ID_Kho = 1;
                    //else
                    //    if (i == 50)
                    //        continue;
                    //    else
                    //        if (i <= 401)
                    //            objTon.ID_Kho = 2;
                    //        else
                    //            if (i == 402)
                    //                continue;
                    //            else
                    //                objTon.ID_Kho = 3;

                    //6. VKHK 8 - 13
                    //String MaLoaiVatTu = "A0100400000000";
                    //if (i <= 9)
                    //    objTon.ID_Kho = 2;
                    //else
                    //    if (i == 10)
                    //        continue;
                    //    else
                    //        objTon.ID_Kho = 3;

                    //7. DU 8 - 38
                    //String MaLoaiVatTu = "A0100700000000";
                    //objTon.ID_Kho = 5;

                    //8. KIMLOAI 8 - 81
                    //String MaLoaiVatTu = "A0100600000000";
                    //objTon.ID_Kho = 5;
                   
                    //9. XANGDAU 8 - 16
                    //String MaLoaiVatTu = "A0300100000000";
                    //objTon.ID_Kho = 6;

                    
                    
                    //10. DAC CHUNG 8 - 108
                    //String MaLoaiVatTu = "A0100800000000";
                    //objTon.ID_Kho = 8;

                    //11. DUNG CU 110
                    //String MaLoaiVatTu = "A0101000000000";
                    //objTon.ID_Kho = 8;

                    //12. OTO 114 - 150
                    //String MaLoaiVatTu = "A0100900000000";
                    //objTon.ID_Kho = 8;

                    //11. NGUYEN VAT LIEU PHU 153 - 163
                    String MaLoaiVatTu = "A0200000000000";
                    objTon.ID_Kho = 8;
                    



                    
                    String KH = "";
                                                           
                    String vatTu = "";
                    vatTu = Convert.ToString(osheetQTCB.Cells[i, 5].Value);
                    

                    //1. KIEM TRA VAT TU DA TON TAI TRONG DANH MUC CHUA
                    // Trả về ID_VatTu
                    int ID_VatTu = 0;

                    String MaKyHieu = Convert.ToString(osheetQTCB.Cells[i, 6].Value);
                    if (MaKyHieu == null)
                        MaKyHieu = "";


                    var objVatTu = (from p in dc.tblVatTus
                                    where p.VatTu.ToUpper() == vatTu.ToUpper() && p.MaKyHieu.ToUpper() == MaKyHieu.ToUpper() && p.Ma_LoaiVatTu == MaLoaiVatTu 
                                    select p).SingleOrDefault();

                    if (objVatTu == null)
                    {
                        //2. INSERT tblVATTU
                        CVatTu m_VatTu = new CVatTu();
                        tblVatTu newVatTu = new tblVatTu();
                        newVatTu.VatTu = vatTu;
                        newVatTu.Ma_LoaiVatTu = MaLoaiVatTu;
                        newVatTu.MaKyHieu = MaKyHieu;
                        // LAY MA VAT TU MOI
                        newVatTu.Ma_VatTu = m_VatTu.GetNewMaVatTu(MaLoaiVatTu, true);
                        newVatTu.Cuoi = true;

                        //DVT 7
                        String DVT = "";
                        DVT = Convert.ToString(osheetQTCB.Cells[i, 7].Value);

                        if (DVT == null)
                        {
                            MessageBox.Show("Không có đơn vị tính: hàng " + i.ToString() );
                            continue; 
                        }
                        
                        CDVT m_DVT = new CDVT();
                        tblDVT objDVT = m_DVT.SelectRecordByName(DVT);
                        if (objDVT == null)
                        {
                            MessageBox.Show("Chưa có DVT này " + DVT);
                            // Insert DVT
                            tblDVT newDVT = new tblDVT();
                            newDVT.DVT = DVT;
                            m_DVT.Insert(newDVT);
                            newVatTu.ID_DVT = newDVT.ID_DVT;
                        }
                        else
                        {
                            newVatTu.ID_DVT = objDVT.ID_DVT;
                        }
                        

                        m_VatTu.Insert(newVatTu);

                        ID_VatTu = newVatTu.ID_VatTu;
                    }
                    else
                    {
                        ID_VatTu = objVatTu.ID_VatTu;
                    }

                    //2. LAY MA VAT TU MAX THEO LOAI VAT TU

                    //4. INSERT tblTON
                    //String SoThe = Convert.ToString(osheetQTCB.Cells[i, 1].Value);
                    String ViTri = Convert.ToString(osheetQTCB.Cells[i, 2].Value);



                    Double DonGia = Convert.ToDouble(osheetQTCB.Cells[i, 8].Value);
                    Double SLTonKH = Convert.ToDouble(osheetQTCB.Cells[i, 9].Value);
                    
                    
                    objTon.ID_VatTu = ID_VatTu;                                      
                    objTon.Gia = DonGia;
                    objTon.NgayTaoTon = DateTime.Now;
                    objTon.SLTonKH = SLTonKH;
                    objTon.SLTonSS = SLTonKH;
                    objTon.SLTonTH = SLTonKH;
                    objTon.SLSS11 = SLTonKH;
                    //objTon.SoTheKho = SoThe;
                    objTon.TangKeKhoang = ViTri;

                    //NguonVatTu 4
                    String NguonVatTu = Convert.ToString(osheetQTCB.Cells[i, 4].Value);
                    CNguonVatTu m_nguonvattu = new CNguonVatTu();
                    tblNguonVatTu obj_NguonVatTu = m_nguonvattu.SelectRecordByKH(NguonVatTu);
                    objTon.ID_NguonVatTu = obj_NguonVatTu.ID_NguonVatTu;
                    
                    //Kho
                    //objTon.ID_Kho = 1;

                    //CCL 11, 12, 13, 14
                    String CCL_1 = Convert.ToString(osheetQTCB.Cells[i, 10].Value);
                    String CCL_2 = Convert.ToString(osheetQTCB.Cells[i, 11].Value);
                    String CCL_3a = Convert.ToString(osheetQTCB.Cells[i, 12].Value);
                    String CCL_3b = Convert.ToString(osheetQTCB.Cells[i, 13].Value);
                    if (CCL_1 == null)
                    {
                        if(CCL_2 == null)
                        {
                            if(CCL_3a == null)
                            {
                                if(CCL_3b == null)
                                {
                                    MessageBox.Show("Không có cấp chất lượng " + i.ToString());
                                }
                                else
                                {
                                    objTon.ID_CCL = 4;
                                }   
                            }
                            else
                            {
                                objTon.ID_CCL = 3;
                            }
                        }
                        else
                        {
                            objTon.ID_CCL = 2;
                        }
                    }
                    else
                    {
                        objTon.ID_CCL = 1;
                    }
                    


                    //HopDong 3
                    String HopDong = Convert.ToString(osheetQTCB.Cells[i, 3].Value);

                    if (HopDong != null)
                    { 
                        //Lấy ID_HopDong
                        CHopDong m_HopDong = new CHopDong();                        
                        tblHopDong objHopDong = m_HopDong.SelectRecordByName(HopDong);
                        if (objHopDong == null)
                        {
                            //MessageBox.Show("Chưa có Hop Dong này " + HopDong);
                            // Insert HopDong
                            tblHopDong newHopDong = new tblHopDong();
                            newHopDong.SoHopDong = HopDong;
                            m_HopDong.Insert(newHopDong);
                            objTon.ID_HopDong = newHopDong.ID_HopDong;
                        }
                        else
                        {
                            objTon.ID_HopDong = objHopDong.ID_HopDong;
                        }

                        
                    }
                    // Nguon Goc
                    String NguonGoc = Convert.ToString(osheetQTCB.Cells[i, 15].Value);
                    objTon.NguonGoc = NguonGoc;
                    ////Nam SX 16
                    //String NamSX = Convert.ToString(osheetQTCB.Cells[i, 16].Value);
                    //if (NamSX == null)
                    //{
                    //    objTon.NamSX = "";//00/0000
                    //}
                    //else 
                    //{
                    //    objTon.NamSX = NamSX;//00/0000
                    //}
                    objTon.NamSX = "";

                    if (objTon.SLTonKH > 0)
                    {
                        CTon m_Ton = new CTon();
                        m_Ton.InsertTonDauKy(objTon);
                    }                                   

                }

                MessageBox.Show("Chuyển số liệu xong");

            }
        }

        public void ToExcel_PhieuNhap(int ID_DPNX)
        {
            QLVTDataContext dc = new QLVTDataContext();
            //Chuyen so lieu tu excel sang store and don vi
            //Microsoft.Office.Interop.Excel._Application oXL; 
            object xlApp;
            
            //Excel._Application xlApp;
            Workbook xlBook;
            Worksheet xlSheet;
            
            //xlApp = new Excel._Application();
            Type t = Type.GetTypeFromProgID("Excel.Application");
            xlApp = System.Activator.CreateInstance(t);

            String link = System.Windows.Forms.Application.StartupPath + "\\PHIEUNHAP_template.xltx";
            xlBook = ((Microsoft.Office.Interop.Excel.Application)xlApp).Workbooks.Open(link);
            xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            ((Microsoft.Office.Interop.Excel.Application)xlApp).Visible = true;
            
            //I. DPNX
            CDPNX m_DPNX = new CDPNX();
            tblDPNX objDPNX = m_DPNX.SelectRecordByID(ID_DPNX);
            //Số phiếu
            String SoPhieu;
            SoPhieu = GetSoPhieu(ID_DPNX);            
            xlSheet.Cells[3, 2] = "Số N: " + SoPhieu;

            //Người nhập
            xlSheet.Cells[4, 6] = objDPNX.NguoiGiaoNhan;
            //Đơn vị bán
            xlSheet.Cells[5, 6] = objDPNX.tblDonVi.DonVi;
            //Địa chỉ
            xlSheet.Cells[6, 6] = objDPNX.tblDonVi.DiaChi;
            //Hoá đơn bán hàng
            xlSheet.Cells[7, 6] = objDPNX.HoaDon;
            //Kho nhập
            xlSheet.Cells[8, 6] = objDPNX.tblKho.Kho;
            //Mục đích - Lý do nhập xuất
            xlSheet.Cells[9, 6] = objDPNX.LyDoNhapXuat;
        
            //II. SLNX
            CSLNX m_SLNX = new CSLNX();
            var listSLNX = m_SLNX.SelectAllRecord(ID_DPNX);

            int row0 = 11;
            int i = 1;
            int stt = 1;
            int dongdem = 0; // số dòng trắng thêm vào để qua trang.
            Excel.Range mRange;
            int len = listSLNX.Count();

            foreach (tblSLNX objSLNX in listSLNX)
            {
                //STT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 1], xlSheet.Cells[row0 + i * 2 - 1, 1]];
                mRange.Value = stt;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                

                //VATTU
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 2], xlSheet.Cells[row0 + i * 2 - 1, 2]];
                mRange.Value = objSLNX.tblTon.tblVatTu.VatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //MAKYHIEU
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 2], xlSheet.Cells[row0 + i * 2, 2]];
                mRange.Value = objSLNX.tblTon.tblVatTu.MaKyHieu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //DVT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 5], xlSheet.Cells[row0 + i * 2 - 1, 5]];
                mRange.Value = objSLNX.tblTon.tblVatTu.tblDVT.DVT;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;                 

                //GIA 6
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 6], xlSheet.Cells[row0 + i * 2 - 1, 6]];
                mRange.Value = objSLNX.tblTon.Gia;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                //SLKH 7
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 7], xlSheet.Cells[row0 + i * 2 - 1, 7]];
                mRange.Value = objSLNX.SLKH;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                mRange.NumberFormat = "#,###";

                //ThanhTien 8 - 9 Merge
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 8], xlSheet.Cells[row0 + i * 2 - 1, 9]];
                mRange.Merge();
                mRange.Value = objSLNX.ThanhTien;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                xlSheet.Cells[row0 + i * 2 - 1, 10] = objSLNX.tblTon.tblNguonVatTu.KH_NguonVatTu;
                xlSheet.Cells[row0 + i * 2 - 1, 11] = objSLNX.tblTon.TangKeKhoang;
                xlSheet.Cells[row0 + i * 2 - 1, 12] = objSLNX.tblTon.SoTheKho + "-" + objSLNX.tblTon.Code.ToString();
                if (objSLNX.tblTon.ID_HopDong != null)
                    xlSheet.Cells[row0 + i * 2 - 1, 13] = objSLNX.tblTon.tblHopDong.SoHopDong;

                // Border
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                var border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                border.LineStyle = Excel.XlLineStyle.xlDot;
                border.Weight = Excel.XlBorderWeight.xlThin;
                
                i++;
                stt++;
                int min_trang1, max_trang1, min_trang2, max_trang2; // số hàng nhỏ nhất và lớn nhất trong 1 trang in.
                min_trang1 = 12;
                max_trang1 = 16;
                min_trang2 = 17;
                max_trang2 = 22;

                if(len>12)
                {
                  
                    if (len <= max_trang1)
                    {
                        if (i == len)
                        {
                            //hàng thứ len-1 sẽ qua trang
                            //bổ sung các hàng trống để đủ max trang 1 từ len - 1
                            for (int dong = 0; dong < max_trang1 - len + 1; dong++)
                            {
                                i++;
                                // Border
                                //mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                                //border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                                //border.LineStyle = Excel.XlLineStyle.xlDot;
                                //border.Weight = Excel.XlBorderWeight.xlThin;
                            }

                            // Border các cột
                            for (int col = 1; col <= 13; col++)
                            {
                                if (!((col == 3) || (col == 4) || (col == 9)))
                                {
                                    mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (len - 1) * 2, col]];
                                    var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                    border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                    border_col.Weight = Excel.XlBorderWeight.xlThin;
                                }
                            }
                            mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (len - 1) * 2, 13]];
                            var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                            border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                            border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;

                            //

                            // Border các cột trang 2
                            for (int col = 1; col <= 13; col++)
                            {
                                if (!((col == 3) || (col == 4) || (col == 9)))
                                {
                                    mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2 + 1, col], xlSheet.Cells[row0 + (i) * 2, col]];
                                    var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                    border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                    border_col.Weight = Excel.XlBorderWeight.xlThin;
                                }
                            }
                            mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2 + 1, 13], xlSheet.Cells[row0 + (i) * 2, 13]];
                            var border_col_cuoi1 = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                            border_col_cuoi1.LineStyle = Excel.XlLineStyle.xlContinuous;
                            border_col_cuoi1.Weight = Excel.XlBorderWeight.xlThin;
                        }

                        
                    }
                    else
                    {
                        //trang 2 và các trang còn lại
                        // Border các cột
                        for (int col = 1; col <= 13; col++)
                        {
                            if (!((col == 3) || (col == 4) || (col == 9)))
                            {
                                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
                                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                border_col.Weight = Excel.XlBorderWeight.xlThin;
                            }
                        }
                        mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
                        var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                        border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                        border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;
                    }
                    
                }
                else
                {
                    if(i>len)
                    {
                        // bổ sung thêm các dòng trắng cho đủ 12 hàng.
                        i--; // trả về vị trí hàng cuối cùng
                        for(int dong = 0; dong < min_trang1 - len; dong++)
                        {
                            i++;
                            // Border
                            mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                            border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                            border.LineStyle = Excel.XlLineStyle.xlDot;
                            border.Weight = Excel.XlBorderWeight.xlThin;
                        }
                        i++;
                        // Border các cột
                        for (int col = 1; col <= 13; col++)
                        {
                            if (!((col == 3) || (col == 4) || (col == 9)))
                            {
                                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
                                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                border_col.Weight = Excel.XlBorderWeight.xlThin;
                            }

                        }
                        mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
                        var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                        border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                        border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;
                    }
                }                    
            }

            // Border cuối
            mRange = xlSheet.Range[xlSheet.Cells[row0 + (i-1) * 2, 1], xlSheet.Cells[row0 + (i-1) * 2, 13]];
            var border_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            border_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_cuoi.Weight = Excel.XlBorderWeight.xlThin;

            //// Border các cột
            //for (int col = 1; col <= 13; col++)
            //{
            //    if(!((col == 3) || (col == 4) || (col == 9)))
            //    {
            //        mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
            //        var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
            //        border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
            //        border_col.Weight = Excel.XlBorderWeight.xlThin;
            //    }
                
            //}
            //mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
            //var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
            //border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            //border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;


                //III. DUOI PHIEU
                i--;

            int k;
            if (i < 13)
                k = 36;
            else            
                k = row0 + i * 2 + 1;            

            mRange = xlSheet.Range[xlSheet.Cells[k, 2], xlSheet.Cells[k, 2]];
            mRange.Value = "TỔNG CỘNG :";
            mRange.Font.Bold = true;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            mRange = xlSheet.Range[xlSheet.Cells[k, 8], xlSheet.Cells[k, 9]];
            mRange.Merge();
            mRange.Font.Bold = true;
            mRange.Value = objDPNX.TongGiaTri;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            mRange.NumberFormat = "#,##0";

            
            mRange = xlSheet.Range[xlSheet.Cells[k + 1, 2], xlSheet.Cells[k + 1, 2]];
            mRange.Value = "Số tiền bằng chữ :";
            mRange.Font.Bold = true;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            mRange = xlSheet.Range[xlSheet.Cells[k + 1, 3], xlSheet.Cells[k + 1, 3]];
            mRange.Font.Italic = true;            
            String TongTien = CThanh_Utilities.DocSo(Convert.ToDouble(objDPNX.TongGiaTri)).Trim();
            if (TongTien.Length < 80)
                mRange.Value = TongTien;
            else
            {
                //> 80 xuống hàng
                int vitri = 0;
                for(int h = 0; h<TongTien.Length; h++)
                {
                    if (TongTien.Substring(h, 1) == " ")
                        vitri = h;

                    if (h > 80)
                        h = TongTien.Length;
                }
                mRange.Value = TongTien.Substring(0,vitri);
                mRange = xlSheet.Range[xlSheet.Cells[k + 2, 3], xlSheet.Cells[k + 2, 3]];
                mRange.Font.Italic = true;
                mRange.Value = TongTien.Substring(vitri + 1, TongTien.Length - vitri - 1);
            }         

            
            

            String NgayPhieu;
            NgayPhieu = "Ngày: " + Convert.ToDateTime(objDPNX.NgayPhieu).ToString("dd/MM/yyyy");
            mRange = xlSheet.Range[xlSheet.Cells[k + 3, 9], xlSheet.Cells[k + 3, 12]];
            mRange.Merge();
            mRange.Font.Italic = true;
            mRange.Value = NgayPhieu;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            
            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 2], xlSheet.Cells[k + 4, 2]];
            mRange.Value = "NGƯỜI LẬP PHIẾU";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 3], xlSheet.Cells[k + 4, 3]];
            mRange.Value = "NGƯỜI GIAO";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 5], xlSheet.Cells[k + 4, 5]];
            mRange.Value = "THỦ KHO";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 7], xlSheet.Cells[k + 4, 7]];
            mRange.Value = "TP. VẬT TƯ";
            mRange.Font.Name = "Cambria";
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            mRange.Font.Bold = true;
            
            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 9], xlSheet.Cells[k + 4, 12]];
            mRange.Merge();
            mRange.Value = "THỦ TRƯỞNG ĐƠN VỊ";
            mRange.Font.Name = "Cambria";
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            mRange.Font.Bold = true;   
          
        //    Dim iRowsCount = i - 1
        //Dim mColsCount = 12

        //'border for all
        //mRange = xlSheet.Range(xlSheet.Cells(iRow0, 1), xlSheet.Cells(iRow0 + iRowsCount, mColsCount)) '.Select()
        //With mRange.Borders(Excel.XlBordersIndex.xlEdgeLeft)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        //With mRange.Borders(Excel.XlBordersIndex.xlEdgeTop)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        //With mRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        //With mRange.Borders(Excel.XlBordersIndex.xlEdgeRight)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        //With mRange.Borders(Excel.XlBordersIndex.xlInsideVertical)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        //With mRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
        //    .LineStyle = Excel.XlLineStyle.xlContinuous
        //    .Weight = Excel.XlBorderWeight.xlThin
        //End With
        }

        public void ToExcel_PhieuXuat(int ID_DPNX)
        {
            QLVTDataContext dc = new QLVTDataContext();
            //Chuyen so lieu tu excel sang store and don vi
            //Microsoft.Office.Interop.Excel._Application oXL; 
            object xlApp;

            //Excel._Application xlApp;
            Workbook xlBook;
            Worksheet xlSheet;

            //xlApp = new Excel._Application();
            Type t = Type.GetTypeFromProgID("Excel.Application");
            xlApp = System.Activator.CreateInstance(t);

            String link = System.Windows.Forms.Application.StartupPath + "\\PHIEUXUAT_template.xltx";
            xlBook = ((Microsoft.Office.Interop.Excel.Application)xlApp).Workbooks.Open(link);
            xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            ((Microsoft.Office.Interop.Excel.Application)xlApp).Visible = true;

            //I. DPNX
            CDPNX m_DPNX = new CDPNX();
            tblDPNX objDPNX = m_DPNX.SelectRecordByID(ID_DPNX);
            //Số phiếu
            String SoPhieu;         
            SoPhieu = GetSoPhieu(ID_DPNX);
            xlSheet.Cells[3, 2] = "Số X: " + SoPhieu;

            //Người nhận hàng
            xlSheet.Cells[4, 6] = objDPNX.tblDonVi.DonVi;

            //Sản phẩm
            if (objDPNX.ID_SanPham != null)
                xlSheet.Cells[5, 6] = objDPNX.tblSanPham.SanPham;
            //Mục đích - Lý do nhập xuất
            xlSheet.Cells[6, 6] = objDPNX.LyDoNhapXuat;
            //Số phương án
            xlSheet.Cells[7, 6] = objDPNX.HoaDon;
            //Kho Xuất
            xlSheet.Cells[8, 6] = objDPNX.tblKho.Kho;



            //II. SLNX
            CSLNX m_SLNX = new CSLNX();
            var listSLNX = m_SLNX.SelectAllRecord(ID_DPNX);

            
            int row0 = 11;
            int stt = 1;
            int i = 1;
            int dongdem = 0; // số dòng trắng thêm vào để qua trang.
            int len = listSLNX.Count();
            Excel.Range mRange;

            foreach (tblSLNX objSLNX in listSLNX)
            {
                //STT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 1], xlSheet.Cells[row0 + i * 2 - 1, 1]];
                mRange.Value = stt;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                
                //VATTU
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 2], xlSheet.Cells[row0 + i * 2 - 1, 2]];
                mRange.Value = objSLNX.tblTon.tblVatTu.VatTu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //MAKYHIEU
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 2], xlSheet.Cells[row0 + i * 2, 2]];
                mRange.Value = objSLNX.tblTon.tblVatTu.MaKyHieu;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                //DVT
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 5], xlSheet.Cells[row0 + i * 2 - 1, 5]];
                mRange.Value = objSLNX.tblTon.tblVatTu.tblDVT.DVT;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;  

                //GIA 6
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 6], xlSheet.Cells[row0 + i * 2 - 1, 6]];
                mRange.Value = objSLNX.tblTon.Gia;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                //SLKH 7
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 7], xlSheet.Cells[row0 + i * 2 - 1, 7]];
                mRange.Value = objSLNX.SLKH;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                mRange.NumberFormat = "#,###";

                //ThanhTien 8 - 9 Merge
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2 - 1, 8], xlSheet.Cells[row0 + i * 2 - 1, 9]];
                mRange.Merge();
                mRange.Value = objSLNX.ThanhTien;
                mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
                mRange.NumberFormat = "#,###";

                xlSheet.Cells[row0 + i * 2 - 1, 10] = objSLNX.tblTon.tblNguonVatTu.KH_NguonVatTu;
                xlSheet.Cells[row0 + i * 2 - 1, 11] = objSLNX.tblTon.TangKeKhoang;
                xlSheet.Cells[row0 + i * 2 - 1, 12] = objSLNX.tblTon.SoTheKho + "-" + objSLNX.tblTon.Code.ToString();
                xlSheet.Cells[row0 + i * 2 - 1, 13] = objSLNX.tblTon.NguonGoc;

                // Border
                mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                var border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                border.LineStyle = Excel.XlLineStyle.xlDot;
                border.Weight = Excel.XlBorderWeight.xlThin;

                i++;                
                stt++;
                int min_trang1, max_trang1, min_trang2, max_trang2; // số hàng nhỏ nhất và lớn nhất trong 1 trang in.
                min_trang1 = 12;
                max_trang1 = 16;
                min_trang2 = 17;
                max_trang2 = 22;

                if (len > 12)
                {

                    if (len <= max_trang1)
                    {
                        if (i == len)
                        {
                            //hàng thứ len-1 sẽ qua trang
                            //bổ sung các hàng trống để đủ max trang 1 từ len - 1
                            for (int dong = 0; dong < max_trang1 - len + 1; dong++)
                            {
                                i++;
                                // Border
                                //mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                                //border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                                //border.LineStyle = Excel.XlLineStyle.xlDot;
                                //border.Weight = Excel.XlBorderWeight.xlThin;
                            }

                            // Border các cột
                            for (int col = 1; col <= 13; col++)
                            {
                                if (!((col == 3) || (col == 4) || (col == 9)))
                                {
                                    mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (len - 1) * 2, col]];
                                    var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                    border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                    border_col.Weight = Excel.XlBorderWeight.xlThin;
                                }
                            }
                            mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (len - 1) * 2, 13]];
                            var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                            border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                            border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;

                            //

                            // Border các cột trang 2
                            for (int col = 1; col <= 13; col++)
                            {
                                if (!((col == 3) || (col == 4) || (col == 9)))
                                {
                                    mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2 + 1, col], xlSheet.Cells[row0 + (i) * 2, col]];
                                    var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                    border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                    border_col.Weight = Excel.XlBorderWeight.xlThin;
                                }
                            }
                            mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2 + 1, 13], xlSheet.Cells[row0 + (i) * 2, 13]];
                            var border_col_cuoi1 = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                            border_col_cuoi1.LineStyle = Excel.XlLineStyle.xlContinuous;
                            border_col_cuoi1.Weight = Excel.XlBorderWeight.xlThin;
                        }


                    }
                    else
                    {
                        //trang 2 và các trang còn lại
                        // Border các cột
                        for (int col = 1; col <= 13; col++)
                        {
                            if (!((col == 3) || (col == 4) || (col == 9)))
                            {
                                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
                                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                border_col.Weight = Excel.XlBorderWeight.xlThin;
                            }
                        }
                        mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
                        var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                        border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                        border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;
                    }

                }
                else
                {
                    if (i > len)
                    {
                        // bổ sung thêm các dòng trắng cho đủ 12 hàng.
                        i--; // trả về vị trí hàng cuối cùng
                        for (int dong = 0; dong < min_trang1 - len; dong++)
                        {
                            i++;
                            // Border
                            mRange = xlSheet.Range[xlSheet.Cells[row0 + i * 2, 1], xlSheet.Cells[row0 + i * 2, 13]];
                            border = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
                            border.LineStyle = Excel.XlLineStyle.xlDot;
                            border.Weight = Excel.XlBorderWeight.xlThin;
                        }
                        i++;
                        // Border các cột
                        for (int col = 1; col <= 13; col++)
                        {
                            if (!((col == 3) || (col == 4) || (col == 9)))
                            {
                                mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
                                var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                                border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                                border_col.Weight = Excel.XlBorderWeight.xlThin;
                            }

                        }
                        mRange = xlSheet.Range[xlSheet.Cells[row0, 13], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
                        var border_col_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeRight];
                        border_col_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
                        border_col_cuoi.Weight = Excel.XlBorderWeight.xlThin;
                    }
                }     

            }

            // Border cuối
            mRange = xlSheet.Range[xlSheet.Cells[row0 + (i - 1) * 2, 1], xlSheet.Cells[row0 + (i - 1) * 2, 13]];
            var border_cuoi = mRange.Borders[Excel.XlBordersIndex.xlEdgeBottom];
            border_cuoi.LineStyle = Excel.XlLineStyle.xlContinuous;
            border_cuoi.Weight = Excel.XlBorderWeight.xlThin;

            // Border các cột
            for (int col = 1; col <= 14; col++)
            {
                if (!((col == 3) || (col == 4) || (col == 9)))
                {
                    mRange = xlSheet.Range[xlSheet.Cells[row0, col], xlSheet.Cells[row0 + (i - 1) * 2, col]];
                    var border_col = mRange.Borders[Excel.XlBordersIndex.xlEdgeLeft];
                    border_col.LineStyle = Excel.XlLineStyle.xlContinuous;
                    border_col.Weight = Excel.XlBorderWeight.xlThin;
                }

            }

            //III. DUOI PHIEU
            i--;

            int k;
            if (i < 13)
                k = 36;
            else
                k = row0 + i * 2 + 1;

            mRange = xlSheet.Range[xlSheet.Cells[k, 2], xlSheet.Cells[k, 2]];
            mRange.Value = "TỔNG CỘNG :";
            mRange.Font.Bold = true;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            mRange = xlSheet.Range[xlSheet.Cells[k, 8], xlSheet.Cells[k, 9]];
            mRange.Merge();
            mRange.Font.Bold = true;
            mRange.Value = objDPNX.TongGiaTri;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            mRange.NumberFormat = "#,##0";


            mRange = xlSheet.Range[xlSheet.Cells[k + 1, 2], xlSheet.Cells[k + 1, 2]];
            mRange.Value = "Số tiền bằng chữ :";
            mRange.Font.Bold = true;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            mRange = xlSheet.Range[xlSheet.Cells[k + 1, 3], xlSheet.Cells[k + 1, 3]];
            mRange.Font.Italic = true;
            String TongTien = CThanh_Utilities.DocSo(Convert.ToDouble(objDPNX.TongGiaTri)).Trim();
            if (TongTien.Length < 80)
                mRange.Value = TongTien;
            else
            {
                //> 80 xuống hàng
                int vitri = 0;
                for (int h = 0; h < TongTien.Length; h++)
                {
                    if (TongTien.Substring(h, 1) == " ")
                        vitri = h;

                    if (h > 80)
                        h = TongTien.Length;
                }
                mRange.Value = TongTien.Substring(0, vitri);
                mRange = xlSheet.Range[xlSheet.Cells[k + 2, 3], xlSheet.Cells[k + 2, 3]];
                mRange.Font.Italic = true;
                mRange.Value = TongTien.Substring(vitri + 1, TongTien.Length - vitri - 1);
            }




            String NgayPhieu;
            NgayPhieu = "Ngày: " + Convert.ToDateTime(objDPNX.NgayPhieu).ToString("dd/MM/yyyy");
            mRange = xlSheet.Range[xlSheet.Cells[k + 3, 9], xlSheet.Cells[k + 3, 12]];
            mRange.Merge();
            mRange.Font.Italic = true;
            mRange.Value = NgayPhieu;
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;


            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 2], xlSheet.Cells[k + 4, 2]];
            mRange.Value = "NGƯỜI LẬP PHIẾU";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 3], xlSheet.Cells[k + 4, 3]];
            mRange.Value = "THỦ KHO";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 5], xlSheet.Cells[k + 4, 5]];
            mRange.Value = "NGƯỜI NHẬN";
            mRange.Font.Name = "Cambria";
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 7], xlSheet.Cells[k + 4, 7]];
            mRange.Value = "TP. VẬT TƯ";
            mRange.Font.Name = "Cambria";
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            mRange.Font.Bold = true;

            mRange = xlSheet.Range[xlSheet.Cells[k + 4, 9], xlSheet.Cells[k + 4, 12]];
            mRange.Merge();
            mRange.Value = "THỦ TRƯỞNG ĐƠN VỊ";
            mRange.Font.Name = "Cambria";
            mRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            mRange.Font.Bold = true;   
        }

        public String GetNewSoPhieu(String NX, DateTime d, String DuoiPhieu, ref int SoPhieu)
        {
            
            int q = 0;
            QLVTDataContext dc = new QLVTDataContext();

            q = Convert.ToInt32((from p in dc.tblDPNXes
                                 where p.NX == NX 
                                 && Convert.ToDateTime(p.NgayPhieu).Year == d.Year && Convert.ToDateTime(p.NgayPhieu).Month == d.Month 
                                 && p.DuoiPhieu == DuoiPhieu
                                 select p.SoPhieu).Max());
            q = q + 1;
            SoPhieu = q;

            CKho m_kho = new CKho();
            
            String DauPhieu = "";
            if (q < 10)
                DauPhieu = "0" + q.ToString() + DuoiPhieu;
            else
                DauPhieu = q.ToString() + DuoiPhieu;
            
            
            String ThangNam = "";            
            if (d.Month.ToString().Length == 1)
                ThangNam = "0" + d.Month.ToString() + "-" + d.Year.ToString().Substring(2, 2);
            else
                ThangNam = d.Month.ToString() + "-" + d.Year.ToString().Substring(2, 2);

            return DauPhieu + "/" + ThangNam;
        }

        public String GetSoPhieu(int ID_DPNX)
        {
            QLVTDataContext dc = new QLVTDataContext();

            var q = (from p in dc.tblDPNXes
                                 where p.ID_DPNX == ID_DPNX                                 
                                 select p).SingleOrDefault();
            

            CKho m_kho = new CKho();

            String DauPhieu = "";
            if (q.SoPhieu < 10)
                DauPhieu = "0" + q.SoPhieu.ToString() + q.DuoiPhieu;
            else
                DauPhieu = q.SoPhieu.ToString() + q.DuoiPhieu;

            DateTime d = Convert.ToDateTime(q.NgayPhieu);

            String ThangNam = "";
            if (d.Month.ToString().Length == 1)
                ThangNam = "0" + d.Month.ToString() + "-" + d.Year.ToString().Substring(2, 2);
            else
                ThangNam = d.Month.ToString() + "-" + d.Year.ToString().Substring(2, 2);

            return DauPhieu + "/" + ThangNam;
        }

        public String GetNewSoTheKho(int ID_Kho, int ID_VatTu)
        {
            //1. Get Nhom The Kho
            String NhomThe = "";
            QLVTDataContext dc = new QLVTDataContext();

            int ID_VatTuCha;
            CVatTu m_vattu = new CVatTu();

            ID_VatTuCha = m_vattu.SelectRecordByMaVatTu(m_vattu.SelectRecordByID(ID_VatTu).Ma_LoaiVatTu).ID_VatTu;

            var q = (from p in dc.tblNhomTheKhos
                    where p.ID_Kho == ID_Kho & p.ID_VatTu == ID_VatTuCha
                    select p).SingleOrDefault();
            if (q == null)
            {
                MessageBox.Show("Nhóm thẻ kho chưa được tạo trước");

                return "";
            }
            NhomThe = q.NhomTheKho;

            //2. Lay max So The Kho trong Ton
            var sothekho = (from p in dc.tblTons
                               where p.SoTheKho.Substring(0, NhomThe.Length) == NhomThe
                                select Int32.Parse(p.SoTheKho.Substring(NhomThe.Length, 3))).ToList();
            int max_SoTheKho = 0;
            if (sothekho.Count != 0)
                max_SoTheKho = (from k in sothekho select k).Max();

            max_SoTheKho += 1;

            //if (max_SoTheKho == 0)
            //    max_SoTheKho = 1;
            //else
            //    max_SoTheKho += 1;

            String SoTheKho = "";
            for (int i = 0; i < 3 - max_SoTheKho.ToString().Length;i++)// Max so the kho la 3 so
            {
                SoTheKho += "0";
            }
                return NhomThe + SoTheKho + max_SoTheKho.ToString();
        }
        
    }

    
}


